﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CLEXA.DataContracts;

namespace CLEXA.DAL
{
    public sealed class DatabaseDAL : IDatabaseDAL
    {
        private const string DATABASE_ID_COL = "database_id";
        private const string USER_ID_COL = "user_id";
        private const string CONNECTION_STRING_COL = "connection_string";

        private const string DELETE_SP = "clexa.sp_database_delete";
        private const string INSERT_SP = "clexa.sp_database_insert";
        private const string SELECT_SP = "clexa.sp_database_select";
        private const string SELECT_BY_DATABASE_ID_SP = "clexa.sp_database_select_by_database_id";
        private const string SELECT_BY_USER_ID_SP = "clexa.sp_database_select_by_user_id";
        private const string UPDATE_SP = "clexa.sp_database_update";

        private readonly string connectionString;

        public DatabaseDAL(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public void Delete(int databaseId)
        {
            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(DELETE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(DATABASE_ID_COL, databaseId);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public int Insert(int userId, string connectionString)
        {
            int result;

            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(INSERT_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter databaseIdParam = cmd.CreateParameter();
                    databaseIdParam.Direction = ParameterDirection.Output;
                    databaseIdParam.ParameterName = DATABASE_ID_COL;
                    databaseIdParam.Size = 4;
                    databaseIdParam.DbType = DbType.Int32;
                    cmd.Parameters.Add(databaseIdParam);

                    cmd.Parameters.AddWithValue(USER_ID_COL, userId);
                    cmd.Parameters.AddWithValue(CONNECTION_STRING_COL, connectionString);
                    cmd.ExecuteNonQuery();

                    result = (int)databaseIdParam.Value;
                }
            }

            return result;
        }

        public List<DatabaseData> Select()
        {
            List<DatabaseData> result = new List<DatabaseData>();

            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(SELECT_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int databaseIdCol = reader.GetOrdinal(DATABASE_ID_COL);
                        int userIdCol = reader.GetOrdinal(USER_ID_COL);
                        int connectionStringCol = reader.GetOrdinal(CONNECTION_STRING_COL);

                        while (reader.Read())
                        {
                            result.Add(new DatabaseData(
                                databaseId : reader.GetInt32(databaseIdCol),
                                userId : reader.GetInt32(userIdCol),
                                connectionString : reader.GetString(connectionStringCol)
                                ));
                        }
                    }
                }
            }

            return result;
        }

        public DatabaseData SelectByDatabaseId(int databaseId)
        {
            DatabaseData result;

            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(SELECT_BY_DATABASE_ID_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue(DATABASE_ID_COL, databaseId);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int databaseIdCol = reader.GetOrdinal(DATABASE_ID_COL);
                        int userIdCol = reader.GetOrdinal(USER_ID_COL);
                        int connectionStringCol = reader.GetOrdinal(CONNECTION_STRING_COL);

                        if (reader.Read())
                        {
                            result = new DatabaseData(
                                databaseId: reader.GetInt32(databaseIdCol),
                                userId: reader.GetInt32(userIdCol),
                                connectionString: reader.GetString(connectionStringCol)
                                );
                        }
                        else
                        {
                            result = null;
                        }
                    }
                }
            }

            return result;
        }

        public List<DatabaseData> SelectByUserId(int userId)
        {
            List<DatabaseData> result = new List<DatabaseData>();

            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(SELECT_BY_USER_ID_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue(USER_ID_COL, userId);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int databaseIdCol = reader.GetOrdinal(DATABASE_ID_COL);
                        int userIdCol = reader.GetOrdinal(USER_ID_COL);
                        int connectionStringCol = reader.GetOrdinal(CONNECTION_STRING_COL);

                        while (reader.Read())
                        {
                            result.Add(new DatabaseData(
                                databaseId: reader.GetInt32(databaseIdCol),
                                userId: reader.GetInt32(userIdCol),
                                connectionString: reader.GetString(connectionStringCol)
                                ));
                        }
                    }
                }
            }

            return result;
        }

        public void Update(DatabaseData database)
        {
            using (SqlConnection conn = new SqlConnection(this.connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(UPDATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(DATABASE_ID_COL, database.DatabaseId);
                    cmd.Parameters.AddWithValue(USER_ID_COL, database.UserId);
                    cmd.Parameters.AddWithValue(CONNECTION_STRING_COL, database.ConnectionString);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
